package com.neuedu.my12306.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import com.neuedu.my12306.dao.DBHelper;
import com.neuedu.my12306.dao.ITicketInforDao;
import com.neuedu.my12306.entity.TicketInfo;

public class ITicketInforDaoImpl implements ITicketInforDao{

	@Override
	public boolean reg(TicketInfo ticketInfo) {
		boolean ret = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "insert into TICKET (id, startstation, endstation, startdate, starttime, username) values (SEQ_TICKET_ID.nextval,?,?,?,?,?)"; 
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, ticketInfo.getStartStation());
			pstmt.setString(2, ticketInfo.getEndStation());
			pstmt.setDate(3,  new java.sql.Date(ticketInfo.getStartDate().getTime()));
			pstmt.setTime(4,  new java.sql.Time(ticketInfo.getStartTime().getTime()));
			pstmt.setString(5, ticketInfo.getUserName());
			int count = pstmt.executeUpdate();
			if (count==1) {
				ret = true;
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		
		return ret;
		
	}

	@Override
	public int findTotal() {
		int total = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "select count(*) from ticket";
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			res = pstmt.executeQuery();
			if(res.next()) {
				total = res.getInt(1);//查询记录总数的sql返回一行一列数据，即返回记录总数
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		return total;
	}

	@Override
	public List<TicketInfo> findTicketInfos(int page, int size) {
		List<TicketInfo> list = new ArrayList<TicketInfo>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "select id, startstation, endstation, startdate, starttime, USERNAME"
				+ " from ( select t.*,rownum rn from ticket t  where rownum <=?) where rn >? ";
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, page*size);
			pstmt.setInt(2, (page-1)*size);
			res = pstmt.executeQuery();
			while (res.next()) {
				int id = res.getInt("id");
				String startStation = res.getString("startStation");
				String endStation = res.getString("endStation");
				java.util.Date startDate = res.getDate("startdate");
				java.util.Date startTime = res.getTime("starttime");
				String userName = res.getString("USERNAME");
				TicketInfo iTicketInfo = new TicketInfo(id, startStation, endStation, startDate, startTime, userName);
				list.add(iTicketInfo);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		return list;
	}

	@Override
	public boolean deleteByIds(int[] ids) {
		boolean ret = false;
		Connection conn = DBHelper.openConnection();
		PreparedStatement pstmt=null ;
		ResultSet rs = null;
		String sql =  "delete from ticket where id=?";
		
		try {
			pstmt = conn.prepareStatement(sql);
			for (int id : ids) {
				pstmt.setInt(1, id);
				pstmt.addBatch();
			}
			int []counts = pstmt.executeBatch();
			int success=0;
			for (int c : counts) {
				if(c==Statement.SUCCESS_NO_INFO||c>0) {
					success++;
				}
			}
			System.out.println("成功删除"+success+"条数据！");
			ret = true;
		} catch (SQLException e) {
			
			
		}finally {
			DBHelper.closeAll(rs, pstmt, conn);
		}
		
		return ret;
	}

	@Override
	public TicketInfo findTicketInfoById(int id) {
		TicketInfo ticketInfo=null;
		Connection conn = null;
		ResultSet re = null;
		PreparedStatement pstmt = null;
		String sql = "select id,STARTSTATION,ENDSTATION,STARTDATE,STARTTIME,USERNAME from ticket where id=?";
		
		try {
			conn = DBHelper.openConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			re = pstmt.executeQuery();
			if (re.next()) {
				
				String startStation =re.getString("startstation");
				String endStation =re.getString("endstation");
				Date startDate = re.getDate("startdate");
				Date startTime = re.getTime("starttime");
				String userName = re.getString("USERNAME");
				ticketInfo = new TicketInfo(id, startStation, endStation, startDate, startTime, userName);
				
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(re, pstmt, conn);
		}
		
		
		return ticketInfo;
	}

	@Override
	public boolean editTicketInfo(TicketInfo ticketInfo) {
		boolean ret = false;
		Connection conn=null;
		ResultSet res = null;
		PreparedStatement pmt = null;
		String sql="update  TICKET set startstation=?, endstation=?, startdate=?, starttime=? where ID=?";
		
		try {
			conn = DBHelper.openConnection();
			pmt = conn.prepareStatement(sql);
			pmt.setString(1, ticketInfo.getStartStation());
			pmt.setString(2, ticketInfo.getEndStation());
			pmt.setDate(3,  new java.sql.Date(ticketInfo.getStartDate().getTime()));
			pmt.setTime(4,  new java.sql.Time(ticketInfo.getStartTime().getTime()));
			pmt.setInt(5, ticketInfo.getId());
			int count = pmt.executeUpdate();
			if (count == 1) {
				ret = true;
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pmt, conn);
		}
		return ret;
	}

}
